﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using Trackr.Core.DataProvider.SQL;
using System.Runtime.Caching;
using System.IO;

namespace Trackr.Core.DataProvider
{
	public class TrackrDataTable<T> where T : class
	{
		private Type _typeOfT;
		private System.Reflection.PropertyInfo[] _typeOfTProperties;
		private string _tableName;
		private SQLiteUtility<T> _sqlUtility;

		public TrackrDataTable()
		{
			_typeOfT = typeof(T);
			_typeOfTProperties = _typeOfT.GetProperties();
			_tableName = _typeOfT.Name;

			string localAppFolder = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
			string trackrDataFolder = Path.Combine(localAppFolder, "Trackr Timetracking");
			if (!Directory.Exists(trackrDataFolder))
			{
				Directory.CreateDirectory(trackrDataFolder);
			}
			string dbName = "TrackrDatabase.sqlite";
#if DEBUG
			dbName = "TrackrDatabase_DEBUG.sqlite";
#endif
			string dbFilePath = System.IO.Path.Combine(trackrDataFolder, dbName);
			_sqlUtility = new SQLiteUtility<T>(dbFilePath);
		}

		public T this[int id]
		{
			get
			{
				return SingleOrDefault(id);
			}
			set
			{
				Add(value);
			}
		}
		public int Add(T obj)
		{
			var properties = _typeOfTProperties
								.Where(p => p.GetValue(obj, null) != null)
								.Where(p => p.Name.ToLower() != "id");
			var propDic = properties.ToDictionary(p => p.Name, p => p.GetValue(obj, null));
			//IQuery qq = Query.Select
			IQuery insertQuery = Query.Insert(_tableName).Set(propDic);
			return _sqlUtility.ExecuteCommand(insertQuery);
		}

		public int Update(int id, T obj)
		{
			var properties = _typeOfTProperties.Where(p => p.GetValue(obj, null) != null).Where(p => p.Name.ToLower() != "id");
			var propDic = properties.ToDictionary(p => p.Name, p => p.GetValue(obj, null));
			IQuery updateQuery = Query.Update(_tableName).Set(propDic).Where("id", Operator.Equals, id.ToString());
			return _sqlUtility.ExecuteCommand(updateQuery);
		}
		public int Remove(int id)
		{
			IQuery deleteQuery = Query.Delete(_tableName).Where("id", Operator.Equals, id.ToString());
			return _sqlUtility.ExecuteCommand(deleteQuery);
		}

		public T GetLatestEntry()
		{
			string cacheKey = string.Format("{0}:LatestEntry", _typeOfT.Name);
			return CacheUtility.Get<T>(cacheKey, () =>
			{
				IQuery query = Query.Select("*").From(_tableName).Where("id", Operator.Equals, string.Format("(select max(id) from {0})", _tableName));
				return _sqlUtility.ExecuteReader(query).SingleOrDefault();
			});
		}

		public T SingleOrDefault(int id)
		{
			string cacheKey = string.Format("{0}:ID:{1}", _tableName, id);
			return CacheUtility.Get<T>(cacheKey, () =>
			{
				IQuery query = Query.Select("*").From(_tableName).Where("id", Operator.Equals, id.ToString());
				return _sqlUtility.ExecuteReader(query).Single();
			});
		}
		public IEnumerable<T> Get()
		{
			string cacheKey = string.Format("{0}:AllItems", _tableName);
			return CacheUtility.Get<IEnumerable<T>>(cacheKey, () =>
			{
				IQuery query = Query.Select("*").From(_tableName);
				return _sqlUtility.ExecuteReader(query);
			});
		}

		public void Truncate()
		{
			string sql = string.Format("drop table {0}", _tableName);
			_sqlUtility.ExecuteCommand(sql);
			_sqlUtility.Create(_tableName);

		}
	}
}
